ADDRESSBASE PREMIUM Database

The AddressBase Premium data model contains ten tables. Nine tables store the actual data and a tenth table stores metadata.

Illustration Courtesy: Ordnance Survey document ‘AddressBaseTM Premium – CSV Technical Specification’.

Back to top of page

Table definition – Street

Copy

street

CREATE TABLE street
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  usrn bigint NOT NULL,
  record_type smallint,
  swa_org_ref_naming integer NOT NULL,
  state smallint,
  state_date date,
  street_surface smallint,
  street_classification smallint,
  "version" integer,
  street_start_date date NOT NULL,
  street_end_date date,
  last_update_date date NOT NULL,
  record_entry_date date,
  street_start_x double precision NOT NULL,
  street_start_y double precision NOT NULL,
  street_end_x double precision NOT NULL,
  street_end_y double precision NOT NULL,
  street_tolerance integer NOT NULL,
  geometry geometry,
  CONSTRAINT street_pkey PRIMARY KEY (usrn)
)

Back to top of page

Table definition – Street Descriptor

 

Copy 
CREATE TABLE street_descriptor
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  usrn bigint NOT NULL,
  street_description character varying(100) NOT NULL,
  locality_name character varying(35),
  town_name character varying(30),
  administrative_area character varying(30),
  "language" character(3),
  CONSTRAINT street_descriptor_pkey PRIMARY KEY (usrn)
)

Back to top of page

Table definition – BLPU

Copy 
CREATE TABLE blpu
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  logical_status smallint NOT NULL,
  blpu_state smallint,
  blpu_state_date date,
  parent_uprn bigint,
  x_coordinate double precision NOT NULL,
  y_coordinate double precision NOT NULL,
  rpc smallint NOT NULL,
  logical_custodian_code integer,
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  postal_address character(1),
  postcode_locator character(8),
  multi_occ_count integer,
  geometry geometry,
  CONSTRAINT blpu_pkey PRIMARY KEY (uprn)
)

Back to top of page

Table definition – Application Cross Reference

Copy 
CREATE TABLE application_xref
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  xref_key character(14) NOT NULL,
  cross_reference character(50) NOT NULL,
  "version" integer,
  source character(6),
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  CONSTRAINT application_xref_pkey PRIMARY KEY (xref_key, uprn)
)

Back to top of page

Table definition – LPI

Copy 
CREATE TABLE lpi
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  lpi_key character(14) NOT NULL,
  "language" character varying(3),
  logical_status smallint NOT NULL,
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  sao_start_number integer,
  sao_start_suffix character(2),
  sao_end_number integer,
  sao_end_suffix character(2),
  sao_text character varying(90),
  pao_start_number integer,
  pao_start_suffix character(2),
  pao_end_number integer,
  pao_end_suffix character(2),
  pao_text character varying(90),
  usrn bigint NOT NULL,
  usrn_match_indicator character(1),
  area_name character varying(35),
  lpi_level character varying(30),
  official_flag character(1),
  CONSTRAINT lpi_pkey PRIMARY KEY (lpi_key)
)

Back to top of page

Table definition – Delivery Point Address

Copy 
CREATE TABLE dpaddress
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  parent_addressable_uprn bigint,
  rm_udprn bigint,
  organisation_name character varying(100),
  department_name character varying(60),
  sub_building_name character varying(30),
  building_name character varying(100),
  building_number integer,
  dependent_thoroughfare_name character varying(80),
  thoroughfare_name character varying(80),
  double_dependent_locality character varying(35),
  dependent_locality character varying(35),
  post_town character varying(30),
  postcode character(8),
  postcode_type character(1),
  welsh_dependent_thoroughfare_name character varying(80),
  welsh_thoroughfare_name character varying(80),
  welsh_double_dependent_locality character varying(35),
  welsh_dependent_locality character varying(35),
  welsh_post_town character varying(30),
  po_box_number character varying(6),
  process_date date,
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  CONSTRAINT dpaddress_pkey PRIMARY KEY (uprn, pro_order)
)

Back to top of page

Table definition – Successor Record

Copy 
CREATE TABLE successor_record
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  succ_key character(14) NOT NULL,
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  successor bigint NOT NULL,
  CONSTRAINT successor_record_pkey PRIMARY KEY (succ_key)
)

Back to top of page

Table definition – Organisation

Copy 
CREATE TABLE organisation
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  org_key character(14) NOT NULL,
  organisation_name character varying(100),
  legal_name character varying(60),
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  CONSTRAINT organisation_pkey PRIMARY KEY (org_key)
)

Back to top of page

Table definition – Classification

Copy 
CREATE TABLE classification
(
  record_identifier integer,
  change_type character(1) NOT NULL,
  pro_order bigint NOT NULL,
  uprn bigint NOT NULL,
  class_key character(14) NOT NULL,
  classification_code character(6),
  class_scheme character varying(60),
  scheme_version real,
  start_date date NOT NULL,
  end_date date,
  last_update_date date NOT NULL,
  entry_date date,
  feature_code integer,
  CONSTRAINT classification_pkey PRIMARY KEY (class_key)
)

Back to top of page

Table definition – Metadata

Copy 
CREATE TABLE metadata
(
  record_identifier integer,
  gaz_name character(60),
  gaz_scope character(60),
  ter_of_use character(60),
  linked_data character(100),
  gaz_owner character(15),
  ngaz_freq character(1),
  custodian_name character(40),
  custodian_uprn bigint,
  local_custodian_code integer,
  co_ord_system character(40),
  co_ord_unit character(10),
  meta_date date,
  class_scheme character(60),
  gaz_date date,
  "language" character(3),
  character_set character(30)
)

Back to top of page

The following columns are added to the following tables by the Address Loader:

  • street - geometry (except Microsoft Access)
  • blpu - geometry (except Microsoft Access)
  • classification - feature_code

Note: The Address Loader does not create a concatenated address field with AddressBase Premium.

The Address Loader will (if requested) create one or two ‘gazetteer-friendly’ views, i.e. ones with no spatial column. By default these views contain the columns listed below.

Note: In Oracle a name-shortening algorithm has been applied to ensure the aliased column names in the views are unique within 30 characters.

Gazetter view - lpi

  • blpu_uprn
  • blpu_logical_status
  • blpu_x_coordinate
  • blpu_y_coordinate
  • blpu_postal_address
  • blpu_postcode_locator
  • organisation_organisation_name
  • lpi_logical_status
  • lpi_sao_start_number
  • lpi_sao_start_suffix
  • lpi_sao_end_number
  • lpi_sao_end_suffix
  • lpi_sao_text
  • lpi_pao_start_number
  • lpi_pao_start_suffix
  • lpi_pao_end_number
  • lpi_pao_end_suffix
  • lpi_pao_text
  • lpi_usrn
  • lpi_usrn_match_indicator
  • lpi_area_name
  • lpi_lpi_level
  • lpi_official_flag
  • street_descriptor_street_description

Gazetteer view - paf

  • blpu_uprn
  • blpu_logical_status
  • blpu_x_coordinate
  • blpu_y_coordinate
  • blpu_postal_address
  • blpu_postcode_locator
  • dpaddress_rm_udprn
  • dpaddress_organisation_name
  • dpaddress_department_name
  • dpaddress_sub_building_name
  • dpaddress_building_name
  • dpaddress_building_number
  • dpaddress_dependent_thoroughfare_name
  • dpaddress_thoroughfare_name
  • dpaddress_double_dependent_locality
  • dpaddress_dependent_locality
  • dpaddress_post_town
  • dpaddress_postcode
  • dpaddress_postcode_type
  • dpaddress_welsh_dependent_thoroughfare_name
  • dpaddress_welsh_thoroughfare_name
  • dpaddress_welsh_double_dependent_locality
  • dpaddress_welsh_dependent_locality
  • dpaddress_welsh_post_town
  • dpaddress_po_box_number

AddressBase Premium classifications consist of the nine Primary codes with Secondary codes, Tertiary codes and Quaternary codes.

The Address Loader creates feature codes based on just the Primary codes. The full OS class is available in the table classification in column classification_code.